package com.bookmall.dao;

import com.bookmall.model.Account;

import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * Created by Squking on 2018/5/1.
 */
public class AccountDAO {
    private static String SQL_InsertUser = "INSERT INTO users(logname, password, phone, email, address, realName) VALUES (?, ?, ?, ?, ?, ?)";
    private static String SQL_SearchUser = "SELECT * FROM users WHERE logname=\'%s\' AND password=\'%s\'";
    private static String SQL_SearchUserWithoutPassword = "SELECT * FROM users WHERE logname=\'%s\'";
    private static String SQL_UpdatePassword = "UPDATE users SET password=\'%s\' WHERE logname=\'%s\' AND password=\'%s\'";
    private static String SQL_UpdateAccount = "UPDATE users SET phone=\'%s\', email=\'%s\', address=\'%s\', realname=\'%s\' WHERE logname=\'%s\'";

    public void insert(Account account) throws Exception {
        PreparedStatement pstmt = null ;
        DBConnection dbc = null ;
        // 下面是针对数据库的具体操作
        try{
            // 连接数据库
            dbc = new DBConnection() ;
            pstmt = dbc.getConnection().prepareStatement(SQL_InsertUser) ;
            pstmt.setString(1, account.getName());
            pstmt.setString(2, account.getPassword());
            pstmt.setInt(3, account.getPhone());
            pstmt.setString(4, account.getEmail());
            pstmt.setString(5, account.getAddress());
            pstmt.setString(6, account.getRealName());
            // 进行数据库更新操作
            pstmt.executeUpdate() ;
            pstmt.close() ;
        }catch (Exception e){
            throw new Exception("操作出现异常") ;
        }
        finally{
            // 关闭数据库连接
            dbc.close() ;
        }
    }

    public Account getAccount(String name) throws Exception
    {
        String sql = String.format(SQL_SearchUserWithoutPassword, name);
        PreparedStatement pstmt = null ;
        DBConnection dbc = null ;
        // 下面是针对数据库的具体操作
        try{
            // 连接数据库
            dbc = new DBConnection() ;
            pstmt = dbc.getConnection().prepareStatement(sql) ;
            // 进行数据库更新操作
            ResultSet resultSet = pstmt.executeQuery();
            Account account = null;
            while (resultSet.next())
            {
                String resultName = resultSet.getString(1);
                if (resultName.equals(name))
                {
                    account = new Account();
                    account.setName(resultName);
                    account.setPassword(resultSet.getString(2));
                    account.setPhone(resultSet.getInt(3));
                    account.setEmail(resultSet.getString(4));
                    account.setAddress(resultSet.getString(5));
                    account.setRealName(resultSet.getString(6));
                    break;
                }
            }
            pstmt.close() ;
            return account;
        }catch (Exception e){
            throw new Exception("操作出现异常") ;
        }
        finally{
            // 关闭数据库连接
            dbc.close() ;
        }
    }

    public Account getAccount(String name, String password) throws Exception
    {
        String sql = String.format(SQL_SearchUser, name, password);
        PreparedStatement pstmt = null ;
        DBConnection dbc = null ;
        // 下面是针对数据库的具体操作
        try{
            // 连接数据库
            dbc = new DBConnection() ;
            pstmt = dbc.getConnection().prepareStatement(sql) ;
            // 进行数据库更新操作
            ResultSet resultSet = pstmt.executeQuery();
            Account account = null;
            while (resultSet.next())
            {
                String resultName = resultSet.getString(1);
                String resultPassword = resultSet.getString(2);
                if (resultName.equals(name) && resultPassword.equals(password))
                {
                    account = new Account();
                    account.setName(resultName);
                    account.setPassword(resultPassword);
                    account.setPhone(resultSet.getInt(3));
                    account.setEmail(resultSet.getString(4));
                    account.setAddress(resultSet.getString(5));
                    account.setRealName(resultSet.getString(6));
                    break;
                }
            }
            pstmt.close() ;
            return account;
        }catch (Exception e){
            throw new Exception("操作出现异常") ;
        }
        finally{
            // 关闭数据库连接
            dbc.close() ;
        }
    }

    public void updatePassword(String logname, String oldPassword, String newPassword) throws Exception {
        String sql = String.format(SQL_UpdatePassword, newPassword, logname, oldPassword);
        PreparedStatement pstmt = null ;
        DBConnection dbc = null ;
        // 下面是针对数据库的具体操作
        try{
            // 连接数据库
            dbc = new DBConnection() ;
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.executeUpdate();
            pstmt.close() ;
        }catch (Exception e){
            throw new Exception("操作出现异常") ;
        }
        finally{
            // 关闭数据库连接
            dbc.close() ;
        }
    }

    public void updateAccount(String logname, Account account) throws Exception {
        String sql = String.format(SQL_UpdateAccount, account.getPhone(), account.getEmail(), account.getAddress(), account.getRealName(), logname);
        PreparedStatement pstmt = null ;
        DBConnection dbc = null ;
        // 下面是针对数据库的具体操作
        try{
            // 连接数据库
            dbc = new DBConnection() ;
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.executeUpdate();
            pstmt.close() ;
        }catch (Exception e){
            throw new Exception("操作出现异常") ;
        }
        finally{
            // 关闭数据库连接
            dbc.close() ;
        }
    }
}
